#-----------------------------------------模拟常规业务--------------------------------------------#

# 1、修改客户密码
#张三（卡号为1010 3576 1234 5678）修改银行卡密码为123321
#李四（卡号为1010 3576 1212 1134）修改银行卡密码为123456
UPDATE `cardInfo` SET `password` = '666666' WHERE cardID = '1010357612345678';
UPDATE `cardInfo` SET `password` = '999999' WHERE cardID = '1010357612121134';

#2、办理银行卡挂失
#李四（卡号为1010 3576 1212 1134）因银行卡丢失，申请挂失
UPDATE `cardInfo` SET `IsReportLoss` = 1 WHERE cardID = '1010357612121134';
SELECT `cardID` AS 卡号,`curID` AS 货币,`savingName` AS 储蓄种类,`openDate` AS 开户日期,
`openMoney` AS 开户金额,`balance` AS 余额,`password` AS 密码,`IsReportLoss` AS 是否挂失,
`customerName` AS 客户姓名
FROM `cardInfo`,`deposit`,`userinfo`
WHERE `cardInfo`.savingID = `deposit`.savingID AND `cardInfo`.customerID = `userInfo`.customerID;

#3、统计银行总存入金额和总支出金额
SELECT `tradeType` AS 交易类型,SUM(tradeMoney) AS 总金额
FROM tradeInfo
GROUP BY `tradeType`;

#4、查询本周开户信息
SELECT c.cardID AS 卡号,u.customerName AS 姓名,c.curID AS 货币,d.savingName AS 存款类型,
c.openDate AS 开户日期,c.openMoney AS 开户金额,c.balance AS 余额,c.IsReportLoss AS 账户状态
FROM cardInfo AS c 
INNER JOIN userInfo AS u ON (c.customerID=u.customerID)
INNER JOIN deposit AS d ON (c.savingID=d.savingID)
WHERE WEEK(NOW()) = WEEK(openDate);

#5、查询本月交易金额最高的卡号
SELECT DISTINCT cardID FROM `tradeInfo` WHERE tradeMoney = (
	SELECT MAX(tradeMoney) FROM `tradeInfo` 
	WHERE MONTH(tradeDate) = MONTH(NOW()) AND YEAR(tradeDate) = YEAR(NOW())
)

#6、查询挂失客户
SELECT customerName AS 客户姓名,telephone AS 联系电话 FROM `userInfo`
WHERE customerID IN (
	SELECT customerID FROM `cardInfo` WHERE IsReportLoss = 1
);

#7、催款提醒业务
SELECT u.customerName AS 客户姓名,u.telephone AS 联系电话,c.balance AS 存款金额
FROM `userInfo` AS u INNER JOIN `cardInfo` AS c ON u.customerID = c.customerID
WHERE balance < 200;


#------------------------------------创建、使用客户友好信息视图---------------------------------#

#view_userInfo:输出银行客户记录
DROP VIEW IF EXISTS view_userInfo;
CREATE VIEW view_userInfo
AS
SELECT customerID AS 客户编号,customerName AS 客户姓名,PID AS 身份证号,telephone AS 联系电话,
address AS 地址 FROM `userInfo`;
SELECT * FROM view_userInfo;

#view_cardInfo:输出银行卡记录
DROP VIEW IF EXISTS view_cardInfo;
CREATE VIEW view_cardInfo
AS
SELECT c.cardID AS 卡号,u.customerName AS 客户姓名,c.cardID AS 货币种类,d.savingName AS 存款类型,
c.openDate AS 开户日期,c.balance AS 余额,c.IsReportLoss AS 是否挂失
FROM `cardInfo` AS c,`userInfo` AS u,`deposit` AS d
WHERE c.savingID = d.savingID AND c.customerID = u.customerID;
SELECT * FROM view_cardInfo;

#view_transInfo:输出银行卡的交易记录
DROP VIEW IF EXISTS view_transInfo;
CREATE VIEW view_transInfo
AS
SELECT tradeDate AS 交易日期,tradeType AS 交易类型,cardID AS 卡号,tradeMoney AS 交易金额,remark AS 备注
FROM `tradeInfo`;
SELECT * FROM view_transInfo;


#----------------------------------------使用事务完成转账---------------------------------------#

/*-从卡号为“1010357612121134”的账户中转出300元给卡号为“1010357612345678”的账户-*/
/*即李四转账300元给张三*/

#查看mysql是否自动提交事务
SHOW VARIABLES LIKE '%autocommit%';

INSERT INTO tradeInfo (tradeType,cardID,tradeMoney) VALUES ('支出',1010357612121134,300);
INSERT INTO tradeInfo (tradeType,cardID,tradeMoney) VALUES ('存入',1010357612345678,300);
#关闭mysql自动提交事务
SET autocommit = off;

BEGIN;
UPDATE cardInfo SET balance = balance -300 WHERE cardID = 1010357612121134;
UPDATE cardInfo SET balance = balance +300 WHERE cardID = 1010357612345678;

#提交事务
COMMIT;
#rollback;#回滚事务

#检查测试数据是否正确
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;

